package cn.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import cn.pojo.Member;
import cn.util.DBUtil;
import cn.util.DateUtil;
import cn.util.Page;

public class MemberDaoImpl extends DBUtil implements MemberDao {

	@Override
	public List<Member> getMemberById(Integer id) {
		// 1、建立连接
		Connection ct = getConnection();
		// 2、创建statement对象
		Statement ps = null;
		// 3、创建statement对象
		ResultSet st = null;
		// 4、指明sql语句
		String sql = " select * from member where id <= " + id;
		try {
			// 5、初始化statement对象
			ps = ct.createStatement();
			// 6、执行sql语句
			st = ps.executeQuery(sql);
			List<Member> list = new ArrayList<Member>();
			// 7、分解结果集
			while (st.next()) {
				Member m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getTimestamp("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getTimestamp("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
				list.add(m);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 8、释放资源
			release(ct, ps, st);
		}
		return null;
	}

	// 添加会员，数据库时间只接受sql包中日期对象
	@Override
	public int addMember(Member m) {
		// 1、建立连接
		Connection ct = getConnection();
		// 2、创建statement对象
		Statement ps = null;
		// 3、创建resultSet对象
		ResultSet st = null;
		String sql = " insert into member "//
				+ "(age,birthDay,cardNo,email,gender,"//
				+ "identityNo,passWord,registDate,score,"//
				+ "telNum,userName) values" //
				+ "( '" + m.getAge() + "','" //
				+ DateUtil.convertUtilDateToTimeStamp(m.getBirthDay()) + "','" //
				+ m.getCardNo() + "','"//
				+ m.getEmail() + "','"//
				+ m.getGender() + "','"//
				+ m.getIdentityNo() + "','"//
				+ m.getPassWord() + "','"//
				+ DateUtil.convertUtilDateToTimeStamp(m.getRegistDate()) + "','"//
				+ m.getScore() + "','"//
				+ m.getTelNum() + "','"//
				+ m.getUserName() + "') ; ";
		System.out.println("sql=>>>>" + sql);
		try {
			ps = ct.createStatement();
			// 执行sql语句返回几条受影响，
			int i = ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			// 要求返回结果集：存放的是自增长的id
			st = ps.getGeneratedKeys();
			// 注意必须：提交事务
			ct.commit();
			// 对结果集处理
			if (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				// 事务的回滚
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			// 释放资源
			release(ct, ps, st);
		}
		return 0;
	}

	// 修改：id= 110185 ：有sql注意风险
	@Override
	public int updateMember(Member m) {
		// 获得连接
		Connection ct = getConnection();
		// 创建ps对象
		Statement ps = null;
		String sql = " update member set age ='" + m.getAge() + "',birthDay='"
				+ DateUtil.convertUtilDateToTimeStamp(m.getBirthDay()) + "',cardNo='" + m.getCardNo() + "',email='"
				+ m.getEmail() + "',gender='" + m.getGender() + "',identityNo='" + m.getIdentityNo() + "',passWord='"
				+ m.getPassWord() + "',registDate='" + DateUtil.convertUtilDateToTimeStamp(m.getRegistDate())
				+ "',score='" + m.getScore() + "',telNum='" + m.getTelNum() + "',userName='" + m.getUserName()
				+ "' where id = '" + m.getId() + "' ; ";
		System.out.println(sql);
		try {
			ps = ct.createStatement();
			int i = ps.executeUpdate(sql);
			ct.commit();
			return i;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				// 事务的回滚
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			// 释放资源
			release(ct, ps, null);
		}
		return 0;
	}

	// 执行删除id= 110185：有sql注意风险
	@Override
	public int deleteMember(Integer id) {
		// 获得连接
		Connection ct = getConnection();
		// 创建ps对象
		Statement ps = null;
		String sql = " delete from member where id =  '" + id + "';";
		try {
			ps = ct.createStatement();
			int i = ps.executeUpdate(sql);
			ct.commit();
			return i;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				// 事务的回滚
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			// 释放资源
			release(ct, ps, null);
		}
		return 0;
	}

	// 根据用户名和密码获得会员对象:有sql注意风险
	@Override
	public Member getMemberByPassAndName(String userName, String passWord) {
		Connection ct = getConnection();
		Statement ps = null;
		ResultSet st = null;
		String sql = " select * from member where userName =  '" + userName + "' and passWord= '" + passWord + "' ; ";
		System.out.println(sql);
		try {
			ps = ct.createStatement();
			st = ps.executeQuery(sql);
			Member m = null;
			while (st.next()) {
				m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
			}
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 登录方法，避免sql注入
	@Override
	public Member login(String userName, String passWord) {
		Connection ct = getConnection();
		// sql预处理器类
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = " select * from member where userName = ? and passWord= ? ; ";
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			int num = 1;
			ps.setObject(num++, userName);// 对象第1个?赋值，要指定参数类型
			ps.setString(num++, passWord);// 对象第2个?赋值，要指定参数类型
			st = ps.executeQuery();
			Member m = null;
			while (st.next()) {
				m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
			}
			return m;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 模糊查询包含gg的总数
	@Override
	public int getSumLikeName(String userName) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet rt = null;
		String sql = "select count(id) from member where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += " and userName like ? ; ";
			param.add("%" + userName + "%");
		}
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			// 参数赋值
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			rt = ps.executeQuery();// 不能再添加sql语句
			// 可以不用while
			if (rt.next()) {
				return rt.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, rt);
		}
		return 0;
	}

	// 根据用户名包含gg模糊查询的Member对象，按照生日倒叙查询
	@Override
	public List<Member> getMemberLikeName(String userName) {
		List<Member> list = new ArrayList<Member>();
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "select * from member where 1=1 ;";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && userName.equals("")) {
			sql += "and userName like ? ;";
			param.add("%" + userName + "%");
		} else {
			return list;
		}
		sql += "order by birthDay desc ; ";
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			// 参数赋值
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			st = ps.executeQuery();// 不能再添加sql语句
			// 可以不用while
			if (st.next()) {
				Member m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
				list.add(m);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return list;
	}

	// 分页查询Member
	@Override
	public Page<Member> getMemberByPageNum(Integer pagaNum) {
		Page<Member> page = new Page<Member>();
		page.setTotalCount(getSumLikeName(null));// 总数还没拿到, 总条数，总记录数
		page.setTotalPage();// 总页数
		page.setPageNum(pagaNum);// 当前的页码
		page.setRowNum();// 当前页起始行号
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = " select * from member limit ? , ? ; ";
		List<Member> list = new ArrayList<Member>();
		try {
			ps = ct.prepareStatement(sql);
			ps.setInt(1, page.getRowNum());
			ps.setInt(2, Page.pageCount);
			st = ps.executeQuery();
			while (st.next()) {
				Member m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
				list.add(m);
			}
			page.setList(list);
			System.out.println(sql);
			System.out.println(page);
			return page;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 根据用户名的分页模糊查询
	@Override
	public Page<Member> getMemberByPageNumAndUserName(Integer pageNum, String userName) {
		Page<Member> page = new Page<Member>();
		page.setTotalCount(getSumLikeName(userName));// 总数还没拿到, 总条数，总记录数
		page.setTotalPage();// 总页数
		page.setPageNum(pageNum);// 当前的页码
		page.setRowNum();// 当前页起始行号
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "select * from member where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += "and userName like ? ";
			param.add("%" + userName + "%");
		}
		sql += "order by birthDay desc limit ? ,? ;";
		param.add(page.getRowNum());
		param.add(page.pageCount);
		try {
			// 预处理sql语句,对sql参数赋值
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			// 执行sql语句，获得结果集
			st = ps.executeQuery();
			// 分解结果集
			List<Member> list = new ArrayList<Member>();
			while (st.next()) {
				Member m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
				list.add(m);
			}
			// 对page的list属性赋值
			page.setList(list);
			System.out.println(page);
			System.out.println(sql);
			return page;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 根据用户名、电话模糊分页查询Member对象
	@Override
	public Page<Member> getMemberByUserNameAndTel(Integer pageNum, String userName, String telNum) {
		Page<Member> page = new Page<Member>();
		page.setTotalCount(getSumLikeName(userName, telNum));// 总数还没拿到, 总条数，总记录数
		page.setTotalPage();// 总页数
		page.setPageNum(pageNum);// 当前的页码
		page.setRowNum();// 当前页起始行号
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "select * from member where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += "and userName like ? ";
			param.add("%" + userName + "%");
		}
		if (telNum != null && !telNum.equals("")) {
			sql += "and telNum like ?";
			param.add(telNum + "%");
		}
		sql += "order by birthDay desc limit ? ,? ;";
		param.add(page.getRowNum());
		param.add(page.pageCount);
		try {
			// 预处理sql语句,对sql参数赋值
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			// 执行sql语句，获得结果集
			st = ps.executeQuery();
			// 分解结果集
			List<Member> list = new ArrayList<Member>();
			while (st.next()) {
				Member m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
				list.add(m);
			}
			// 对page的list属性赋值
			page.setList(list);
			System.out.println(page);
			System.out.println(sql);
			return page;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 根据用户名、电话模糊分页查询Member对象的getSumLikeName方法
	private Integer getSumLikeName(String userName, String telNum) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "select count(id) from member where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += "and userName like ? ";
			param.add("%" + userName + "%");
		}
		if (telNum != null && !telNum.equals("")) {
			sql += "and telNum like ? ";
			param.add(telNum + "%");
		}
		sql += ";";
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			// 参数赋值
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			st = ps.executeQuery();
			if (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 根据tel、userName、startBirthday、endBirthday模糊分页查询Member对象
	@Override
	public Page<Member> getMemberByUserNameAndTelAndBirthday(Integer pageNum, String telNum, String userName,
			Date startBirthday, Date endBirthday) {
		String start = DateUtil.convertUtilDateToString(startBirthday);
		String end = DateUtil.convertUtilDateToString(endBirthday);
		if (start != null && end != null && start.compareTo(end) > 0) {
			Date temp = startBirthday;
			startBirthday = endBirthday;
			endBirthday = temp;
		}
		Page<Member> page = new Page<Member>();
		page.setTotalCount(getSumLikeName(userName, telNum, startBirthday, endBirthday));// 总数还没拿到, 总条数，总记录数
		page.setTotalPage();// 总页数
		page.setPageNum(pageNum);// 当前的页码
		page.setRowNum();// 当前页起始行号
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "select * from member where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += "and userName like ? ";
			param.add("%" + userName + "%");
		}
		if (telNum != null && !telNum.equals("")) {
			sql += "and telNum like ? ";
			param.add(telNum + "%");
		}
		if (startBirthday != null && !startBirthday.equals("")) {
			sql += "and birthDay >= ? ";
			param.add(DateUtil.convertUtilDateToSQLDate(startBirthday));
		}
		if (endBirthday != null && !endBirthday.equals("")) {
			sql += "<= ? ";
			param.add(DateUtil.convertUtilDateToSQLDate(endBirthday));
		}
		sql += "order by birthDay desc limit ? ,? ;";
		param.add(page.getRowNum());
		param.add(page.pageCount);
		try {
			// 预处理sql语句,对sql参数赋值
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			// 执行sql语句，获得结果集
			st = ps.executeQuery();
			// 分解结果集
			List<Member> list = new ArrayList<Member>();
			while (st.next()) {
				Member m = new Member();
				m.setAge(st.getInt("age"));
				m.setBirthDay(st.getDate("birthDay"));
				m.setCardNo(st.getString("cardNo"));
				m.setEmail(st.getString("email"));
				m.setGender(st.getString("gender"));
				m.setId(st.getInt("id"));
				m.setIdentityNo(st.getString("identityNo"));
				m.setPassWord(st.getString("passWord"));
				m.setRegistDate(st.getDate("registDate"));
				m.setScore(st.getInt("score"));
				m.setTelNum(st.getString("telNum"));
				m.setUserName(st.getString("userName"));
				list.add(m);
			}
			// 对page的list属性赋值
			page.setList(list);
			System.out.println(page);
			System.out.println(sql);
			return page;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 根据tel、userName、startBirthday、endBirthday模糊分页查询Member对象的getSumLikeName方法
	private Integer getSumLikeName(String telNum, String userName, Date startBirthday, Date endBirthday) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "select count(id) from member where 1=1 ";
		List<Object> param = new ArrayList<Object>();
		if (userName != null && !userName.equals("")) {
			sql += "and userName like ? ";
			param.add("%" + userName + "%");
		}
		if (telNum != null && !telNum.equals("")) {
			sql += "and telNum like ? ";
			param.add(telNum + "%");
		}
		if (startBirthday != null) {
			sql += "and birthDay >= ? ";
			param.add(DateUtil.convertUtilDateToSQLDate(startBirthday));
		}
		if (endBirthday != null) {
			sql += "<= ? ";
			param.add(DateUtil.convertUtilDateToSQLDate(endBirthday));
		}
		sql += ";";
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			// 参数赋值
			for (int i = 0; i < param.size(); i++) {
				ps.setObject((i + 1), param.get(i));
			}
			st = ps.executeQuery();
			if (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 根据id批量删除
	@Override
	public int[] deleteMemberBatch(List<Integer> list) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		String sql = "select * from member where id = ? ;";
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(1, list.get(i));
				ps.addBatch();// 为批处理添加
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// 执行一条删除语句
	public void deleteById() {
		System.out.println(cud(Member.class, " delete from member where id = ? ;", 29)[0] + "条删除成功");
	}

	// 购物增加积分记录，修改积分余额
	@Override
	public Map<String, Integer> updateMemberById(Member m) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = "update member set score = ? where id = ? ";
		String sql1 = "insert score (createDate,message,num,mid) values (?,?,?,?) ;";
		// 执行修改
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, DateUtil.convertUtilDateToTimeStamp(new Date()));
			ps.setObject(2, "购物新增积分10000分");
			ps.setObject(3, m.getScore());
			ps.setObject(4, m.getId());
			int k = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
}
